package com.gljx.util;

import com.gljx.web.Entity.Carmaintain;
import com.gljx.web.Entity.CarmaintainImport;
import com.gljx.web.Entity.StudentImport;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class ImportExecl {
    /**
     * 总行数
     */

    private int totalRows = 0;

    /**
     * 总列数
     */

    private int totalCells = 0;

    /**
     * 错误信息
     */

    private String errorInfo;

    /**
     * 构造方法
     */
    public ImportExecl() {

    }

    /**
     * @描述：得到总行数
     * @参数：@return
     * @返回值：int
     */

    public int getTotalRows() {

        return totalRows;

    }

    /**
     * @描述：得到总列数
     * @参数：@return
     * @返回值：int
     */

    public int getTotalCells() {

        return totalCells;

    }

    /**
     * @描述：得到错误信息
     * @参数：@return
     * @返回值：String
     */

    public String getErrorInfo() {

        return errorInfo;

    }

    /**
     * @描述：验证excel文件
     * @参数：@param filePath　文件完整路径
     * @参数：@return
     * @返回值：boolean
     */

    public boolean validateExcel(String filePath) {

        /** 检查文件名是否为空或者是否是Excel格式的文件 */

        if (filePath == null || !(WDWUtil.isExcel2003(filePath) || WDWUtil.isExcel2007(filePath))) {

            errorInfo = "文件名不是excel格式";

            return false;

        }

        /** 检查文件是否存在 */

        File file = new File(filePath);

        if (file == null || !file.exists()) {

            errorInfo = "文件不存在";

            return false;

        }

        return true;

    }

    /**
     * @描述：根据文件名读取excel文件
     * @参数：@param filePath 文件完整路径
     * @参数：@return
     * @返回值：List
     */
    public Map<String, List<List<String>>> read(String filePath) {
        Map<String, List<List<String>>> map = new HashMap<String, List<List<String>>>();
        //List<List<String>> dataLst = new ArrayList<List<String>>();  

        InputStream is = null;

        try {

            /** 验证文件是否合法 */

            if (!validateExcel(filePath)) {

                System.out.println(errorInfo);

                return null;

            }

            /** 判断文件的类型，是2003还是2007 */

            boolean isExcel2003 = true;

            if (WDWUtil.isExcel2007(filePath)) {

                isExcel2003 = false;

            }

            /** 调用本类提供的根据流读取的方法 */

            File file = new File(filePath);

            is = new FileInputStream(file);

            map = read(is, isExcel2003);
            is.close();

        } catch (Exception ex) {

            ex.printStackTrace();

        } finally {

            if (is != null) {

                try {

                    is.close();

                } catch (IOException e) {

                    is = null;

                    e.printStackTrace();

                }

            }

        }

        /** 返回最后读取的结果 */

        return map;

    }

    /**
     * @描述：根据流读取Excel文件
     * @参数：@param inputStream
     * @参数：@param isExcel2003
     * @参数：@return
     * @返回值：List
     */

    public Map<String, List<List<String>>> read(InputStream inputStream, boolean isExcel2003) {
        Map<String, List<List<String>>> map = new HashMap<String, List<List<String>>>();
        //List<List<String>> dataLst = null;  

        try {

            //** 根据版本选择创建Workbook的方式 *//*  

            Workbook wb = null;

            if (isExcel2003) {
                wb = new HSSFWorkbook(inputStream);
            } else {
                wb = new XSSFWorkbook(inputStream);
            }
            try {
                map = read(wb);
            } catch (Exception e) {
                e.printStackTrace();
            }

        } catch (IOException e) {

            e.printStackTrace();

        }

        return map;

    }

    /**
     * @throws Exception
     * @描述：读取数据
     * @参数：@param Workbook
     * @参数：@return
     * @返回值：List<List<String>>
     */

    private Map<String, List<List<String>>> read(Workbook wb) throws Exception {
        Map<String, List<List<String>>> map = new HashMap<String, List<List<String>>>();
        List<List<String>> dataLst = new ArrayList<List<String>>();
        Map<String, List<List<String>>> map2 = new HashMap<String, List<List<String>>>();
        /** 得到第一个shell */
        if (wb.getNumberOfSheets() == 1) {/**如果是1则是学员考试成绩统计表 */
            Sheet sheet = wb.getSheetAt(0);

            /** 得到Excel的行数 */

            this.totalRows = sheet.getPhysicalNumberOfRows();

            /** 得到Excel的列数 */

            if (this.totalRows >= 1 && sheet.getRow(0) != null) {

                this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();

            }

            if (this.totalCells == 2) {
                if (!"姓名".equals(sheet.getRow(0).getCell(0).toString()) || this.totalCells != 2) {
                    return map2;
                } else {

                    for (int r = 0; r < this.totalRows; r++) {
                        Row row = sheet.getRow(r);
                        if (row == null) {

                            continue;

                        }

                        List<String> rowLst = new ArrayList<String>();//每行的值

                        /** 循环Excel的列 */

                        for (int c = 0; c < this.getTotalCells(); c++) {

                            Cell cell = row.getCell(c);

                            String cellValue = "";

                            if (null != cell) {
                                // 以下是判断数据的类型
                                switch (cell.getCellType()) {
                                    case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                                        cellValue = cell.getNumericCellValue() + "";
                                        break;

                                    case HSSFCell.CELL_TYPE_STRING: // 字符串
                                        cellValue = cell.getStringCellValue();
                                        break;

                                    case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                                        cellValue = cell.getBooleanCellValue() + "";
                                        break;

                                    case HSSFCell.CELL_TYPE_FORMULA: // 公式
                                        cellValue = cell.getCellFormula() + "";
                                        break;

                                    case HSSFCell.CELL_TYPE_BLANK: // 空值
                                        cellValue = "";
                                        break;

                                    case HSSFCell.CELL_TYPE_ERROR: // 故障
                                        cellValue = "非法字符";
                                        break;

                                    default:
                                        cellValue = "未知类型";
                                        break;
                                }
                            }
                            //System.out.println(cellValue+"-------"+c);
                            rowLst.add(cellValue);

                        }

                        /** 保存第r行的第c列 */

                        dataLst.add(rowLst);
                    }
                    map.put("1", dataLst);
                }
                return map;
            }

            //预约分车导入
            if (this.totalCells == 5) {
                if (!"姓名".equals(sheet.getRow(0).getCell(0).toString()) || this.totalCells != 5) {
                    return map2;
                } else {

                    for (int r = 0; r < this.totalRows; r++) {
                        Row row = sheet.getRow(r);
                        if (row == null) {

                            continue;

                        }

                        List<String> rowLst = new ArrayList<String>();//每行的值

                        /** 循环Excel的列 */

                        for (int c = 0; c < this.getTotalCells(); c++) {

                            Cell cell = row.getCell(c);

                            String cellValue = "";

                            if (null != cell) {
                                // 以下是判断数据的类型
                                switch (cell.getCellType()) {
                                    case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                                        cellValue = cell.getNumericCellValue() + "";
                                        break;

                                    case HSSFCell.CELL_TYPE_STRING: // 字符串
                                        cellValue = cell.getStringCellValue();
                                        break;

                                    case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                                        cellValue = cell.getBooleanCellValue() + "";
                                        break;

                                    case HSSFCell.CELL_TYPE_FORMULA: // 公式
                                        cellValue = cell.getCellFormula() + "";
                                        break;

                                    case HSSFCell.CELL_TYPE_BLANK: // 空值
                                        cellValue = "";
                                        break;

                                    case HSSFCell.CELL_TYPE_ERROR: // 故障
                                        cellValue = "非法字符";
                                        break;

                                    default:
                                        cellValue = "未知类型";
                                        break;
                                }
                            }
                            //System.out.println(cellValue+"-------"+c);
                            rowLst.add(cellValue);

                        }

                        /** 保存第r行的第c列 */

                        dataLst.add(rowLst);
                    }
                    map.put("1", dataLst);
                }
                return map;
            }
            //科目二编号 科目三编号导入
            if (this.totalCells == 8) {
                if (!"序号".equals(sheet.getRow(0).getCell(0).toString()) || this.totalCells != 8) {
                    return map2;
                } else {
                    for (int r = 0; r < this.totalRows; r++) {
                        Row row = sheet.getRow(r);
                        if (row == null) {

                            continue;

                        }

                        List<String> rowLst = new ArrayList<String>();//每行的值

                        /** 循环Excel的列 */
                        //System.out.println(this.getTotalCells());
                        for (int c = 0; c < this.getTotalCells(); c++) {

                            Cell cell = row.getCell(c);
                            String cellValue = "";

                            if (null != cell) {
                                // 以下是判断数据的类型
                                switch (cell.getCellType()) {
                                    case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                                        int _cellValue = (int) cell.getNumericCellValue();
                                        cellValue = _cellValue + "";
                                        break;

                                    case HSSFCell.CELL_TYPE_STRING: // 字符串
                                        cellValue = cell.getStringCellValue();
                                        break;

                                    case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                                        cellValue = cell.getBooleanCellValue() + "";
                                        break;

                                    case HSSFCell.CELL_TYPE_FORMULA: // 公式
                                        cellValue = cell.getCellFormula() + "";
                                        break;

                                    case HSSFCell.CELL_TYPE_BLANK: // 空值
                                        cellValue = "";
                                        break;

                                    case HSSFCell.CELL_TYPE_ERROR: // 故障
                                        cellValue = "非法字符";
                                        break;

                                    default:
                                        cellValue = "未知类型";
                                        break;
                                }
                            }
                            //System.out.println(cellValue+"-------"+c);
                            rowLst.add(cellValue);

                        }

                        /** 保存第r行的第c列 */

                        dataLst.add(rowLst);
                    }
                    map.put("1", dataLst);
                }
                return map;
            }


            //this.totalCells==9
            /** 循环Excel的行 */
            System.out.printf(sheet.getRow(0).getCell(0).toString());
            if (!"学员姓名".equals(sheet.getRow(0).getCell(0).toString()) || this.totalCells != 9) {
                throw new Exception("excel格式不正确1");
            } else {
                for (int r = 0; r < this.totalRows; r++) {
                    Row row = sheet.getRow(r);
                    if (row == null) {
                        continue;

                    }

                    List<String> rowLst = new ArrayList<String>();//每行的值

                    /** 循环Excel的列 */

                    for (int c = 0; c < this.getTotalCells(); c++) {

                        Cell cell = row.getCell(c);

                        String cellValue = "";

                        if (null != cell) {
                            // 以下是判断数据的类型
                            switch (cell.getCellType()) {
                                case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                                    cellValue = cell.getNumericCellValue() + "";
                                    break;

                                case HSSFCell.CELL_TYPE_STRING: // 字符串
                                    cellValue = cell.getStringCellValue();
                                    break;

                                case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                                    cellValue = cell.getBooleanCellValue() + "";
                                    break;

                                case HSSFCell.CELL_TYPE_FORMULA: // 公式
                                    cellValue = cell.getCellFormula() + "";
                                    break;

                                case HSSFCell.CELL_TYPE_BLANK: // 空值
                                    cellValue = "";
                                    break;

                                case HSSFCell.CELL_TYPE_ERROR: // 故障
                                    cellValue = "非法字符";
                                    break;

                                default:
                                    cellValue = "未知类型";
                                    break;
                            }
                        }
                        //System.out.println(cellValue+"-------"+c);
                        rowLst.add(cellValue);

                    }

                    /** 保存第r行的第c列 */

                    dataLst.add(rowLst);
                }
                map.put("1", dataLst);
            }

        } else if (wb.getNumberOfSheets() == 4) {
            Sheet sheet = null;
            if (wb.getSheetAt(0).getRow(0).getPhysicalNumberOfCells() != 6 || wb.getSheetAt(1).getRow(0).getPhysicalNumberOfCells() != 10
                    || wb.getSheetAt(2).getRow(0).getPhysicalNumberOfCells() != 10 || wb.getSheetAt(3).getRow(0).getPhysicalNumberOfCells() != 10) {
                throw new Exception("excel格式不正确2");
            }
            if (!"驾校名称".equals(wb.getSheetAt(0).getRow(0).getCell(0).toString())
                    || !"序号".equals(wb.getSheetAt(1).getRow(0).getCell(0).toString())
                    || !"序号".equals(wb.getSheetAt(2).getRow(0).getCell(0).toString()) ||
                    !"序号".equals(wb.getSheetAt(3).getRow(0).getCell(0).toString())) {
                throw new Exception("excel格式不正确3");
            }
            for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                List<List<String>> list = new ArrayList<>();
                sheet = wb.getSheetAt(i);
                /** 得到Excel的行数 */
                this.totalRows = sheet.getPhysicalNumberOfRows();

                /** 得到Excel的列数 */

                if (this.totalRows >= 1 && sheet.getRow(0) != null) {

                    this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();

                }
                //System.out.println(this.totalCells);
                /** 循环Excel的行 */

                for (int r = 0; r < this.totalRows; r++) {

                    Row row = sheet.getRow(r);

                    if (row == null) {

                        continue;

                    }

                    List<String> rowLst = new ArrayList<String>();

                    /** 循环Excel的列 */

                    for (int c = 0; c < this.getTotalCells(); c++) {

                        Cell cell = row.getCell(c);

                        String cellValue = "";

                        if (null != cell) {
                            // 以下是判断数据的类型
                            switch (cell.getCellType()) {
                                case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                                    cellValue = cell.getNumericCellValue() + "";
                                    break;

                                case HSSFCell.CELL_TYPE_STRING: // 字符串
                                    cellValue = cell.getStringCellValue();
                                    break;

                                case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                                    cellValue = cell.getBooleanCellValue() + "";
                                    break;

                                case HSSFCell.CELL_TYPE_FORMULA: // 公式
                                    cellValue = cell.getCellFormula() + "";
                                    break;

                                case HSSFCell.CELL_TYPE_BLANK: // 空值
                                    cellValue = "";
                                    break;

                                case HSSFCell.CELL_TYPE_ERROR: // 故障
                                    cellValue = "非法字符";
                                    break;

                                default:
                                    cellValue = "未知类型";
                                    break;
                            }
                        }
                        rowLst.add(cellValue);

                    }

                    /** 保存第r行的第c列 */

                    list.add(rowLst);
                }
                map.put("" + i + "", list);
            }


            //Sheet sheet = wb.getSheetAt(0);
            //Sheet sheet1 = wb.getSheetAt(1);/**合格*/
            //Sheet sheet2 = wb.getSheetAt(2);/**不合格*/
            //Sheet sheet3 = wb.getSheetAt(3);/**末考*/

        } else {
            throw new Exception("请导入正确的excel");
        }
        return map;

    }


    public Map<String, List<StudentImport>> show(String path)throws Exception {
        Map<String, List<StudentImport>> stusMap = new HashMap<>();
        Map<String, List<List<String>>> map = new HashMap<>();
        map = this.read(path);
        //System.out.println(map.size());
        if (map.size() == 1) {
            List<List<String>> list = map.get("1");
            List<StudentImport> stus = new ArrayList<>();
            for (int i = 1; i < list.size(); i++) {
                StudentImport stu = new StudentImport();
                List<String> cellList = list.get(i);
                //遍历每一行的每一页
                stu.setStuName(cellList.get(0));
                stu.setZkzNum(cellList.get(1));
                stu.setCerno(cellList.get(2));
                stu.setDate(cellList.get(5));
                //stu.setScore(cellList.get(8));
                stu.setCourse(cellList.get(3));
                stu.setType(cellList.get(4));
                //System.err.println("导入的科目:"+cellList.get(3));
                if(cellList.get(3).equals("科目一")){//科目一成绩判断                	
                    if (Double.valueOf(cellList.get(8).trim()) >= 90) {//合格
                         stu.setScore("90");
                    } else if (Double.valueOf(cellList.get(8).trim()) < 90) {//不合格
                          stu.setScore("0");
                    } else if (Double.valueOf(cellList.get(8).trim()) == -1) {//缺考
                          stu.setScore("-1");
                    }          	
                }else if(cellList.get(3).equals("科目二")){//科目二成绩判断
                	if (Double.parseDouble(cellList.get(8).trim()) >= 80) {//合格
                        stu.setScore("90");
                   } else if (Double.parseDouble(cellList.get(8).trim()) < 80) {//不合格
                         stu.setScore("0");
                   } else if (Double.parseDouble(cellList.get(8).trim()) == -1) {//缺考
                         stu.setScore("-1");
                   }         
                }else if(cellList.get(3).equals("科目三")){//科目三成绩判断

                	if (Double.parseDouble(cellList.get(8).trim()) >= 90) {//合格
                        stu.setScore("90");
                   } else if (Double.parseDouble(cellList.get(8).trim()) < 90) {//不合格
                         stu.setScore("0");
                   } else if (Double.parseDouble(cellList.get(8).trim()) == -1) {//缺考
                         stu.setScore("-1");
                   }
                	/*if (Integer.valueOf(cellList.get(8)) >= 90) {
                        stu.setScore("90");
                   } else if (Integer.valueOf(cellList.get(8)) < 90 && Integer.valueOf(cellList.get(8)) > 0) {
                         stu.setScore("60");
                   } else if (Integer.valueOf(cellList.get(8)) == 0) {
                         stu.setScore("0");
                   } else {
                         stu.setScore("-1");//缺考
                   }  */
                }
                /*if (cellList.get(3).equals("科目一")){  //科目一成绩判断
                    if (Integer.valueOf(cellList.get(8)) >= 90) {
                        stu.setScore("90");
                    } else if (Integer.valueOf(cellList.get(8)) < 90 && Integer.valueOf(cellList.get(8)) > 0) {
                        stu.setScore("60");
                    } else if (Integer.valueOf(cellList.get(8)) == 0) {
                        stu.setScore("0");
                    } else if (Integer.valueOf(cellList.get(8)) == -1) {
                        stu.setScore("-1");//缺考
                    }
                } else {
                    //对大车成绩判断
                    if (cellList.get(4).equals("B1") || cellList.get(4).equals("B2")
                            || cellList.get(4).equals("A1") || cellList.get(4).equals("A2") || cellList.get(4).equals("A3")) {
                        if (cellList.get(3).equals("科目二") || cellList.get(3).indexOf("科目三") >= 0) {
                        	//System.out.println("进入科目二成绩:"+cellList.get(8));
                            if (Integer.valueOf(cellList.get(8)) >= 90)
                                stu.setScore("90");
                            else
                                stu.setScore("60");
                        }

                    } else{  //小车成绩判断
                    
                        if (cellList.get(3).equals("科目二")) {
                            if (Integer.valueOf(cellList.get(8)) >= 80)
                                stu.setScore("90");
                            else
                                stu.setScore("60");
                        } else if (cellList.get(3).indexOf("科目三") >= 0) {
                            if (Integer.valueOf(cellList.get(8)) >= 90)
                                stu.setScore("90");
                            else
                                stu.setScore("60");
                        }

                    }
                }*/
                
               //============================
                
                 /*else if(cellList.get(3).equals("科目二")||cellList.get(3).equals("科目三"))
                 {
					 if(Integer.valueOf(cellList.get(8))>=80){
						 stu.setScore("90");
					 }else if((Integer.valueOf(cellList.get(8))<80&&Integer.valueOf(cellList.get(8))>0)
							 ||(Integer.valueOf(cellList.get(8))<0)){
						 stu.setScore("60");
					 }else if(Integer.valueOf(cellList.get(8))==0){
						 stu.setScore("0");
					 }
				 }
				 /*else if(cellList.get(3).equals("科目三")){
					 if(Integer.valueOf(cellList.get(8))>=80){
						 stu.setScore("90");
					 }else if(Integer.valueOf(cellList.get(8))<80&&Integer.valueOf(cellList.get(8))>0){
						 stu.setScore("60");
					 }else if(Integer.valueOf(cellList.get(8))==0){
						 stu.setScore("0");
					 }
				 }*/
                stus.add(stu);
            }
            stusMap.put("1", stus);
        } else if (map.size() > 1) {
            for (int q = 1; q < map.size(); q++) {
                //q==1 合格 q==2不合格 q==3末考
                List<StudentImport> stus = new ArrayList<>();
                List<List<String>> list = map.get("" + q + "");
                for (int i = 1; i < list.size(); i++) {
                    StudentImport stu = new StudentImport();
                    List<String> cellList = list.get(i);
                    if (q == 1) {
                        stu.setScore("90");
                    } else if (q == 2) {
                        stu.setScore("60");
                    } else if (q == 3) {
                        stu.setScore("0");
                    }
                    stu.setZkzNum(cellList.get(2));
                    stu.setStuName(cellList.get(3));
                    stu.setCerno(cellList.get(4));
                    stu.setType(cellList.get(5));
                    stu.setDate(cellList.get(7));
                    if (cellList.get(8).equals("1")) {
                        stu.setCourse("科目一");
                    } else if (cellList.get(8).equals("2")) {
                        stu.setCourse("科目二");
                    } else if (cellList.get(8).equals("3")) {
                        stu.setCourse("科目三");
                    } else {
                        try {
                            throw new Exception("字段格式错误");
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }
                    stus.add(stu);
                   /* System.out.println(cellList.get(4));//身份证
                    System.out.println(cellList.get(5));//准驾车型
	                System.out.println(cellList.get(7));//考试日期
	                System.out.println(cellList.get(8));//考试科目
*/
                }
                stusMap.put("" + q + "", stus);
            }

        } else {//导入Excel不正确
            throw new Exception("导入excel异常");
           /* try {
                throw new Exception("导入excel异常");
            } catch (Exception e) {
                e.printStackTrace();
            }*/
        }
        return stusMap;
    }


    public Map<String, List<StudentImport>> show2(String path) {
        Map<String, List<StudentImport>> stusMap = new HashMap<>();
        Map<String, List<List<String>>> map = new HashMap<>();
        map = this.read(path);
        //System.out.println(map.size());
        if (map.size() == 1) {
            List<List<String>> list = map.get("1");
            List<StudentImport> stus = new ArrayList<>();
            for (int i = 1; i < list.size(); i++) {
                StudentImport stu = new StudentImport();
                List<String> cellList = list.get(i);
                //遍历每一行的每一页
                stu.setStuName(cellList.get(0));
                stu.setCfidcardno(cellList.get(1));
                stus.add(stu);
            }
            stusMap.put("1", stus);
        } else if (map.size() > 1) {
            for (int q = 1; q < map.size(); q++) {
                //q==1 合格 q==2不合格 q==3末考
                List<StudentImport> stus = new ArrayList<>();
                List<List<String>> list = map.get("" + q + "");
                for (int i = 1; i < list.size(); i++) {
                    StudentImport stu = new StudentImport();
                    List<String> cellList = list.get(i);
                    stu.setStuName(cellList.get(0));
                    stu.setCfidcardno(cellList.get(1));
                    stus.add(stu);
                }
                stusMap.put("" + q + "", stus);
            }

        } else {
            try {
                throw new Exception("导入excel异常");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return stusMap;
    }

    public Map<String, List<StudentImport>> show3(String path) {
        Map<String, List<StudentImport>> stusMap = new HashMap<>();
        Map<String, List<List<String>>> map = new HashMap<>();
        map = this.read(path);
        //System.out.println(map.size());
        if (map.size() == 1) {
            List<List<String>> list = map.get("1");
            List<StudentImport> stus = new ArrayList<>();
            for (int i = 1; i < list.size(); i++) {
                StudentImport stu = new StudentImport();
                List<String> cellList = list.get(i);
                //遍历每一行的每一页
                stu.setXuhao(cellList.get(0));
                stu.setStuName(cellList.get(1));
                stu.setCerno(cellList.get(2));
                stu.setZkzNum(cellList.get(3));
                stu.setYksj(cellList.get(4));
                stu.setKsdd(cellList.get(5));
                stu.setKscx(cellList.get(6));
                stu.setZkcx(cellList.get(7));
                stus.add(stu);
            }
            stusMap.put("1", stus);
        } else if (map.size() > 1) {
            for (int q = 1; q < map.size(); q++) {
                //q==1 合格 q==2不合格 q==3末考
                List<StudentImport> stus = new ArrayList<>();
                List<List<String>> list = map.get("" + q + "");
                for (int i = 1; i < list.size(); i++) {
                    StudentImport stu = new StudentImport();
                    List<String> cellList = list.get(i);
                    stu.setXuhao(cellList.get(0));
                    stu.setStuName(cellList.get(1));
                    stu.setCerno(cellList.get(2));
                    stu.setZkzNum(cellList.get(3));
                    stu.setYksj(cellList.get(4));
                    stu.setKsdd(cellList.get(5));
                    stu.setKscx(cellList.get(6));
                    stu.setZkcx(cellList.get(7));
                    stus.add(stu);
                }
                stusMap.put("" + q + "", stus);
            }

        } else {
            try {
                throw new Exception("导入excel异常");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return stusMap;
    }


    public Map<String, List<StudentImport>> show4(String path) {
        Map<String, List<StudentImport>> stusMap = new HashMap<>();
        Map<String, List<List<String>>> map = new HashMap<>();
        map = this.read(path);
        //System.out.println(map.size());
        if (map.size() == 1) {
            List<List<String>> list = map.get("1");
            List<StudentImport> stus = new ArrayList<>();
            for (int i = 1; i < list.size(); i++) {
                StudentImport stu = new StudentImport();
                List<String> cellList = list.get(i);
                //遍历每一行的每一页

                stu.setStuName(cellList.get(0));
                stu.setCerno(cellList.get(1));
                stu.setYyjlNo(cellList.get(2));
                stu.setYyjlName(cellList.get(3));
                stu.setBz(cellList.get(4));
                stus.add(stu);
            }
            stusMap.put("1", stus);
        } else if (map.size() > 1) {
            for (int q = 1; q < map.size(); q++) {
                //q==1 合格 q==2不合格 q==3末考
                List<StudentImport> stus = new ArrayList<>();
                List<List<String>> list = map.get("" + q + "");
                for (int i = 1; i < list.size(); i++) {
                    StudentImport stu = new StudentImport();
                    List<String> cellList = list.get(i);
                    stu.setStuName(cellList.get(0));
                    stu.setCfidcardno(cellList.get(1));
                    stus.add(stu);
                }
                stusMap.put("" + q + "", stus);
            }

        } else {
            try {
                throw new Exception("导入excel异常");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return stusMap;
    }

    /**
     * 关于车辆维修导入
     * @param file
     * @return
     * @throws Exception
     */
    public List<CarmaintainImport> carmaintainImportMap(File file)throws Exception{

      //一个新的集合关于存储CarmaintainImport
        List<CarmaintainImport> listCarmainImport=new ArrayList<>();

       //导入的所有条数
        List<List<Object>> carmaintainImportMap=new ArrayList<>();
        //记录错误条数
        int error=0;
      try{

       //导入吴联发的导入工具类,对文件进行处理
        carmaintainImportMap=new ImportExcelUtil_MrWu().importExcel(file);
        //这个专门针对车辆维修的导入
          String[]cows={"业务日期","车辆","维修项目","数量","单价","维修金额","工时数量","工时","工时金额","合计","部门","修理厂"};
            //判断格式是否正确
          String cfmaintainitemtype="";
          for(int i=0;i<cows.length;i++){
                  if(!"".equals(carmaintainImportMap.get(1).get(i).toString())){
                      if(!cows[i].equals(carmaintainImportMap.get(1).get(i).toString())){
                          error++;
                      }
                  }else{
                      error++;
                  }
                  if(carmaintainImportMap.get(0).toString().indexOf("事故维修")>0){
                      cfmaintainitemtype="2";
                  }else if(carmaintainImportMap.get(0).toString().indexOf("正常维修")>0){
                      cfmaintainitemtype="1";
                  }else if(carmaintainImportMap.get(0).toString().indexOf("公交车维修")>0){
                      cfmaintainitemtype="3";
                  }else{
                      cfmaintainitemtype="1";
                  }
          }

          //如果格式正确
          if(error!=0){

          }else{
              for(int i=2;i<carmaintainImportMap.size();i++){
                  CarmaintainImport carmaintainImport=new CarmaintainImport();
                  carmaintainImport.setBusinessdate(carmaintainImportMap.get(i).get(0).toString());//业务日期
                  carmaintainImport.setCfcarName(carmaintainImportMap.get(i).get(1).toString());//车辆
                  carmaintainImport.setCfmaintainitemName(carmaintainImportMap.get(i).get(2).toString());//维修项目
                  carmaintainImport.setCfamount(new BigDecimal((carmaintainImportMap.get(i).get(3).toString())));//数量
                  carmaintainImport.setCfprice(new BigDecimal((carmaintainImportMap.get(i).get(4).toString())));//单价
                  carmaintainImport.setCfpriceofCarmain(new BigDecimal((carmaintainImportMap.get(i).get(5).toString())));//维修金额
                  carmaintainImport.setCfcountofhours(new BigDecimal((carmaintainImportMap.get(i).get(6).toString())));//工时数量
                  carmaintainImport.setCfmanhours(new BigDecimal((carmaintainImportMap.get(i).get(7).toString())));//工时
                  carmaintainImport.setCfpriceofhours(new BigDecimal((carmaintainImportMap.get(i).get(8).toString())));//工时金额
                  carmaintainImport.setCfallamount(new BigDecimal((carmaintainImportMap.get(i).get(9).toString())));//合计
                  carmaintainImport.setCfdepartmentName(carmaintainImportMap.get(i).get(10).toString());//部门
                  carmaintainImport.setCfrepairshopName(carmaintainImportMap.get(i).get(11).toString());//修理厂
                  carmaintainImport.setCfmaintainitemtype(cfmaintainitemtype);//维修类型
                  listCarmainImport.add(carmaintainImport);
              }
          }
      } catch(Exception e){
          e.printStackTrace();
      }finally{
         if(error!=0){
             return null;
         }

      }
        return listCarmainImport;
    }
}

/**
 * @描述：工具类
 * @时间：2012-08-29 下午16:30:40
 */

class WDWUtil {

    /**
     * @描述：是否是2003的excel，返回true是2003
     * @参数：@param filePath　文件完整路径
     * @参数：@return
     * @返回值：boolean
     */

    public static boolean isExcel2003(String filePath) {

        return filePath.matches("^.+\\.(?i)(xls)$");

    }

    /**
     * @描述：是否是2007的excel，返回true是2007
     * @参数：@param filePath　文件完整路径
     * @参数：@return
     * @返回值：boolean
     */

    public static boolean isExcel2007(String filePath) {

        return filePath.matches("^.+\\.(?i)(xlsx)$");

    }

}  
